﻿using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Reflection;
using Masuit.Tools;
using NPOI.HSSF.UserModel;
using System.Linq;

namespace adb.excel
{
    /// <summary>
    /// 数据出错模型
    /// </summary>
    public class TableErrorModel
    {
        /// <summary>
        /// 行号
        /// </summary>
        public int RowNo { get; set; }
        /// <summary>
        /// 表头名称
        /// </summary>
        public string FieldName { get; set; }
        /// <summary>
        /// 数据
        /// </summary>
        public string Data { get; set; }
        /// <summary>
        /// 错误提示
        /// </summary>
        public string ErrorMsg { get; set; }
    }
    /// <summary>
    /// 数据导出
    /// </summary>
    public class ExcelExport
    {
        /// <summary>
        /// 导出excel表格
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="lst">数据列表</param>
        /// <param name="excelTitles">表头</param>
        /// <returns></returns>
        public static Stream export<T>(List<T> lst, List<tExcelTitle> excelTitles) where T : class
        {
            IWorkbook wb = new XSSFWorkbook();
            ISheet sheet = wb.CreateSheet();
            IRow titleRow = sheet.CreateRow(0);
            int col = 0;
            //创建表头
            foreach (var item in excelTitles)
            {
                ICell cell = titleRow.CreateCell(col++);
                cell.SetCellValue(item.excelTitleName);
            }
            Dictionary<string, PropertyInfo> pros = new Dictionary<string, PropertyInfo>();
            var ps = typeof(T).GetProperties();
            foreach (var p in ps)
            {
                pros.Add(p.Name, p);
            }
            int rowNum = 1;
            //if (false)
            //{
                //填充数据
                foreach (var item in lst)
                {
                    col = 0;
                    IRow dataRow = sheet.CreateRow(rowNum++);
                    foreach (var key in excelTitles)
                    {
                        ICell cell = dataRow.CreateCell(col++);
                        if (pros.ContainsKey(key.excelTitleField))
                        {
                            object? v = pros[key.excelTitleField].GetValue(item);
                            cell.SetCellValue((v == null ? "" : v.ToString()));
                        }
                        else
                            cell.SetCellValue("");
                    }
                    //break;
                }
            //}
            using (var stream = new MemoryStream())
            {
                wb.Write(stream);
                wb.Close();
                var ms = new MemoryStream(stream.ToArray());
                return ms;
            }
        }


        /// <summary>
        /// 导入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="excelTitles"></param>
        /// <param name="stream">xls表格</param>
        /// <returns></returns>
        public static List<T> inputData<T>(List<tExcelTitle> excelTitles, Stream stream) where T : class, new()
        {
            try
            {
                stream.Position = 0;
                IWorkbook wb = new XSSFWorkbook(stream);
                ISheet sheet = wb.GetSheetAt(0);
                IRow titleRow = sheet.GetRow(0);
                int col = 0;
                List<T> lst = new List<T>();
                Dictionary<int, PropertyInfo> pros = new Dictionary<int, PropertyInfo>();
                //创建表头
                foreach (var item in excelTitles)
                {
                    for (int i = 0; i < titleRow.LastCellNum; i++)
                    {
                        ICell cell = titleRow.GetCell(i);
                        if (cell.StringCellValue == item.excelTitleName)
                        {
                            PropertyInfo p = typeof(T).GetProperty(item.excelTitleField);
                            if (p != null)
                            {
                                pros.Add(i, p);
                                break;
                            }
                        }
                    }
                }
                //读取数据
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    T model = new T();
                    IRow row = sheet.GetRow(i);
                    for (int k = 0; k <= row.LastCellNum; k++)
                    {
                        if (pros.ContainsKey(k))
                        {
                            var Cell = row.GetCell(k);
                            var p = pros[k];
                            Type t = p.PropertyType;
                            string v = "";
                            switch (Cell.CellType)
                            {
                                case CellType.Unknown:

                                    break;
                                case CellType.Numeric:
                                    {
                                        if (HSSFDateUtil.IsCellDateFormatted(Cell))
                                        {
                                            v = $"{Cell.DateCellValue}";
                                        }
                                        else
                                            v = $"{Cell.NumericCellValue}";
                                    }
                                    break;
                                case CellType.String:
                                    v = Cell.StringCellValue;
                                    break;
                                case CellType.Formula:
                                    {
                                        HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(wb);
                                        v = eva.Evaluate(Cell).StringValue;
                                    }
                                    break;
                                case CellType.Blank:
                                    break;
                                case CellType.Boolean:
                                    v = $"{(Cell.BooleanCellValue ? "TRUE" : "FALSE")}";
                                    break;

                                case CellType.Error:
                                    break;
                            }

                            p.SetValue(model, v.ConvertTo(t));
                        }

                    }
                    lst.Add(model);
                }
                wb.Close();
                return lst;
            }
            catch (Exception e)
            {
                throw e;
            }
        }


        /// <summary>
        /// 检测导入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="excelTitles"></param>
        /// <param name="stream">xls表格</param>
        /// <returns>第一个是成功条数,第二个是出错条数,第三个是列表</returns>
        public static Tuple<int, int, List<TableErrorModel>> checkInputData(List<tExcelTitle> excelTitles, Stream stream)
        {
            try
            {
                stream.Position = 0;
                IWorkbook wb = new XSSFWorkbook(stream);
                ISheet sheet = wb.GetSheetAt(0);
                IRow titleRow = sheet.GetRow(0);
                Dictionary<int, string> fileTitle = new Dictionary<int, string>();
                List<TableErrorModel> errLst = new List<TableErrorModel>();
                //创建表头
                foreach (var item in excelTitles)
                {
                    var use = true;
                    for (int i = 0; i < titleRow.LastCellNum; i++)
                    {
                        ICell cell = titleRow.GetCell(i);
                        if (cell.StringCellValue == item.excelTitleName)
                        {
                            
                                fileTitle.Add(i, item.excelTitleName);
                                use = false;
                                break;
                        }
                    }
                    if (use)
                    {
                        errLst.Add(new TableErrorModel
                        {
                            Data = "",
                            ErrorMsg = "缺失列",
                            FieldName = item.excelTitleName,
                            RowNo = 1
                        });
                    }
                }
                if (errLst.Any() == false)
                {
                    return new Tuple<int, int, List<TableErrorModel>>(0, sheet.LastRowNum - 1, errLst);
                }
                int errorCount = 0;
                //读取数据
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    var ok = true;
                    IRow row = sheet.GetRow(i);
                    for (int k = 0; k <= row.LastCellNum; k++)
                    {
                        
                            var Cell = row.GetCell(k);
                            string v = "";
                            switch (Cell.CellType)
                            {
                                case CellType.Unknown:

                                    break;
                                case CellType.Numeric:
                                    {
                                        if (HSSFDateUtil.IsCellDateFormatted(Cell))
                                        {
                                            v = $"{Cell.DateCellValue}";
                                        }
                                        else
                                            v = $"{Cell.NumericCellValue}";
                                    }
                                    break;
                                case CellType.String:
                                    v = Cell.StringCellValue;
                                    break;
                                case CellType.Formula:
                                    {
                                        HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(wb);
                                        v = eva.Evaluate(Cell).StringValue;
                                    }
                                    break;
                                case CellType.Blank:
                                    break;
                                case CellType.Boolean:
                                    v = $"{(Cell.BooleanCellValue ? "TRUE" : "FALSE")}";
                                    break;
                                case CellType.Error:
                                    break;
                            }
                            if (string.IsNullOrWhiteSpace(v))
                            {
                                errLst.Add(new TableErrorModel
                                {
                                    Data = v,
                                    ErrorMsg = "值不能为空",
                                    FieldName = fileTitle[i],
                                    RowNo = i
                                });
                                ok = false;
                            }
                    }
                    if (ok == false) errorCount++;
                }
                int successCount = sheet.LastRowNum - 1 - errorCount;
                wb.Close();
                return new Tuple<int, int, List<TableErrorModel>>(successCount, errorCount, errLst);
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
}
